package Dao;

import Interface.TeacherDaoInterface;
import Service.DepartmentService;
import org.example.Department;
import org.example.Teacher;
import util.Condition;
import util.JdbcHelper;
import util.Pagination;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.HashSet;
import java.util.List;

public class TeacherDao implements TeacherDaoInterface {
    private static final TeacherDao teacherDao =new TeacherDao();
    public static TeacherDao getInstance() {
        return teacherDao;
    }

    @Override
    public Collection<Teacher> findAll(Connection connection, List<Condition> conditionList, Pagination pagination) throws SQLException {
        Collection<Teacher> desiredTeachers = new HashSet<Teacher>();
        //准备预编译的语句
        StringBuilder sql_select = new StringBuilder("SELECT * FROM teacher");
        if(conditionList!=null){
            String whereClause = Condition.toWhereClause(conditionList);
            sql_select.append(whereClause);
        }
        int totalNum = CommonDao.getInstance().count(connection,"teacher",conditionList);
        String pginationClause = pagination.toLimitClause(totalNum);
        sql_select.append(pginationClause);
        PreparedStatement pstmt_select_all = connection.prepareStatement(sql_select.toString());
        //执行预编译语句，结果集保存在resultSet中
        ResultSet resultSet_select_all = pstmt_select_all.executeQuery();
        //遍历resultSet
        while (resultSet_select_all.next()) {
            int id = resultSet_select_all.getInt("id");
            String name = resultSet_select_all.getString("name");
            int department_id = resultSet_select_all.getInt("department_id");
            Department department = DepartmentService.getInstance().find(department_id);
            Teacher teacher = new Teacher(id, name,department,department_id);
            desiredTeachers.add(teacher);
        }
        JdbcHelper.close(resultSet_select_all,pstmt_select_all,connection);
        return desiredTeachers;
    }
    @Override
    public Teacher add( Teacher teacher) throws SQLException {
        Connection connection = JdbcHelper.getConn();
        String addTeacher_sql = "INSERT INTO teacher ( id,name, department_id)VALUES"+"(?,?,?)";
        PreparedStatement stmt_add_all = connection.prepareStatement(addTeacher_sql);
        stmt_add_all.setInt(1,teacher.getId());
        stmt_add_all.setString(2,teacher.getName());
        stmt_add_all.setInt(3,teacher.getDepartment_id());
        stmt_add_all.executeUpdate();
        JdbcHelper.close(stmt_add_all,connection);
        //关闭connection对象。只要建立了连接，必须要关闭。
        return null;
    }

    @Override
    public Teacher update(Teacher teacher) throws SQLException {
        Connection connection = JdbcHelper.getConn();
        String updateTeacher_sql1 = "update teacher set name = ? , department_id = ? where id = ?";
        PreparedStatement stmt_update_name = connection.prepareStatement(updateTeacher_sql1);
        stmt_update_name.setString(1,teacher.getName());
        stmt_update_name.setInt(2,teacher.getDepartment_id());
        stmt_update_name.setInt(3,teacher.getId());
        stmt_update_name.executeUpdate();
        JdbcHelper.close(stmt_update_name,connection);
        return null;
    }

    @Override
    public Teacher delete(Teacher teacher) throws SQLException {
        Connection connection = JdbcHelper.getConn();
        String updateTeacher_sql1 = "delete from teacher where id = ?";
        PreparedStatement stmt_update_name = connection.prepareStatement(updateTeacher_sql1);
        stmt_update_name.setInt(1,teacher.getId());
        stmt_update_name.executeUpdate();
        JdbcHelper.close(stmt_update_name,connection);
        return null;
    }
    public Teacher find(Integer id) throws SQLException {
        Teacher desiredTeacher = null;
        Connection connection = JdbcHelper.getConn();
        String sql_select_id = "SELECT * FROM teacher WHERE id = ?";
        PreparedStatement pstmt_select_id = connection.prepareStatement(sql_select_id);
        pstmt_select_id.setLong(1,id);
        ResultSet resultSet = pstmt_select_id.executeQuery();
        if (resultSet.next()){
            int id1 = resultSet.getInt("id");
            String description = resultSet.getString("name");
            int department_id = resultSet.getInt("department_id");
            desiredTeacher = new Teacher(id1,description,department_id);
        }
        JdbcHelper.close(resultSet,pstmt_select_id,connection);
        return desiredTeacher;
    }
}